-- 1. 创建数据库
USE master;
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = 'EmployeeDB')
    DROP DATABASE EmployeeDB;  -- 如果数据库已存在，先删除
GO

CREATE DATABASE EmployeeDB;    -- 创建新数据库
GO

USE EmployeeDB;
GO

-- 2. 创建表结构
CREATE TABLE Departments (
    DeptID INT PRIMARY KEY IDENTITY(1,1),
    DeptName NVARCHAR(50) NOT NULL,
    Location NVARCHAR(100)
);
GO

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY IDENTITY(1,1),
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    DeptID INT FOREIGN KEY REFERENCES Departments(DeptID),
    HireDate DATE DEFAULT GETDATE(),
    Salary DECIMAL(10,2) CHECK (Salary > 0)
);
GO

-- 3. 插入测试数据
INSERT INTO Departments (DeptName, Location)
VALUES 
    ('IT', 'New York'),
    ('HR', 'London'),
    ('Finance', 'Tokyo');
GO

INSERT INTO Employees (FirstName, LastName, DeptID, Salary)
VALUES
    ('John', 'Doe', 1, 75000.00),
    ('Jane', 'Smith', 2, 65000.00),
    ('Mike', 'Johnson', 1, 80000.00);
GO

-- 4. 查询数据（带条件）
SELECT 
    e.FirstName, 
    e.LastName, 
    d.DeptName, 
    e.HireDate,
    e.Salary
FROM Employees e
INNER JOIN Departments d ON e.DeptID = d.DeptID
WHERE e.Salary > 70000;
GO

-- 5. 更新数据（带事务）
BEGIN TRANSACTION;
    UPDATE Employees
    SET Salary = Salary * 1.05  -- 涨薪5%
    WHERE DeptID = 1;           -- 仅针对IT部门
COMMIT TRANSACTION;
GO

-- 6. 删除数据（带错误处理）
BEGIN TRY
    DELETE FROM Departments 
    WHERE DeptName = 'Finance';
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH
GO

-- 7. 创建存储过程（统计部门人数）
CREATE PROCEDURE GetDepartmentEmployeeCount
    @DeptID INT
AS
BEGIN
    SELECT 
        d.DeptName, 
        COUNT(e.EmployeeID) AS TotalEmployees
    FROM Departments d
    LEFT JOIN Employees e ON d.DeptID = e.DeptID
    WHERE d.DeptID = @DeptID
    GROUP BY d.DeptName;
END
GO

-- 调用存储过程
EXEC GetDepartmentEmployeeCount @DeptID = 1;
GO
